package com.sitech.ibnms.c3p.command.sql;

import com.sitech.ibnms.c3p.command.CommandType;
import com.sitech.ibnms.c3p.command.script.ScriptCommand;
import com.sitech.ibnms.c3p.domain.C3pScriptParam;
import com.sitech.ibnms.c3p.domain.ResourceAccount;
import com.sitech.ibnms.c3p.mappers.C3pScriptMapper;
import com.sitech.ibnms.c3p.util.C3pConstants;
import com.sitech.ibnms.c3p.util.JSONUtil;
import com.sitech.ismp.messageObject.cc.CccpResults;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.task.AsyncListenableTaskExecutor;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ConnectionCallback;
import org.springframework.util.Assert;
import org.springframework.util.concurrent.ListenableFuture;
import request.ScriptRequest;

import javax.annotation.Resource;
import java.sql.*;
import java.util.*;
import java.util.Date;
import java.util.concurrent.Callable;

import static com.sitech.ibnms.c3p.util.C3pConstants.OUTVAR_SQL_RESULT_CONTENT;
import static com.sitech.ibnms.c3p.util.C3pConstants.OUTVAR_SQL_RESULT_UNIT_ID;
import static com.sitech.ibnms.c3p.util.C3pConstants.OUTVAR_SQL_RESULT_SIZE;
import static com.sitech.ismp.messageObject.cc.CccpConstants.EXECUTE_FAILURE;
import static com.sitech.ismp.messageObject.cc.CccpConstants.EXECUTE_SUCCESS;
import static org.apache.commons.lang3.time.DateFormatUtils.format;

/**
 * Created by liujhc on 7/14.
 */
public class SQLCommand extends ScriptCommand {


    @Autowired
    private SQLExecutor sqlExecutor;
    @Resource(name = "executor")
    private AsyncListenableTaskExecutor taskExecutor;
    @Autowired
    private C3pScriptMapper c3pScriptMapper;

    @Override
    public CommandType getType() {
        return CommandType.SQL;
    }

    @Override
    public ListenableFuture<CccpResults> doExecuteScript(final ScriptRequest request, final ResourceAccount resourceAccount) {
        return taskExecutor.submitListenable(new Callable<CccpResults>() {
            @Override
            public CccpResults call() throws Exception {
                CccpResults cccpResults = createResultsInstance(request, resourceAccount);
                //只支持查询、调用存储过程。
                //spring有在一个连接里执行sql文件的能力  ，
                switch (SqlStatement.from(request.getContent())) {
                    case SELECT:
                    case CALL:
                        return sqlExecutor.execute(resourceAccount, getAction(request, cccpResults,resourceAccount));
                    default:
                        String time = format(new Date(), DATE_TIME_FORMAT);
                        cccpResults.setBeginTime(time);
                        cccpResults.setEndTime(time);
                        cccpResults.setExecFlag(EXECUTE_FAILURE);
                        cccpResults.setFailedReason("不支持的SQL语句:" + request.getContent());
                        cccpResults.setExecResult(cccpResults.getFailedReason());
                        return cccpResults;
                }
            }
        });
    }

    private ConnectionCallback<CccpResults> getAction(final ScriptRequest request, final CccpResults results,final ResourceAccount resourceAccount) {
        return new ConnectionCallback<CccpResults>() {
            @Override
            public CccpResults doInConnection(Connection con) throws SQLException, DataAccessException {
                results.setResultType(CommandType.SQL.getScriptType());
                results.setBeginTime(format(new Date(), DATE_TIME_FORMAT));
                logger.debug("{}: 开始执行SQL", request.getRequestId());
                final Map<String, String> vars = results.getVariables();
                final List<HashMap<String, String>> kpis = results.getKpis();
                final String extUnitId=resourceAccount.getUnitId();
                final int fetchRows = 256; // 最终结果最多支持的数据条数
                final int maxRows = fetchRows * 5; // 游标能到达的最大位置(越小性能越好)
                String _sql = request.getContent();
                Statement statement = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
                statement.setMaxRows(maxRows);
                ResultSet resultSet = null;
                try {
                    @SuppressWarnings("unchecked")
                    Map<String, String> paramsIn = (Map<String, String>) JSONUtil.fromJSON(request.getInputVars());
                    for (Map.Entry<String, String> entry : paramsIn.entrySet()) {
                        String paramName = entry.getKey();
                        String paramValue = entry.getValue();
                        if (SqlStatement.SELECT.equals(SqlStatement.from(paramValue))) {
                            try {
                                resultSet = statement.executeQuery(paramValue);
                                if (resultSet.next()) {
                                    paramValue = resultSet.getString(1);
                                }
                                resultSet.last();
                                int totalRow = resultSet.getRow();
                                Assert.state(totalRow == 1, String.format("SQL参数[%s]的查询结果必须是1行,而实际为%d行", paramName, totalRow));
                            } catch (Exception e) {
                                throw new IllegalStateException("SQL参数处理失败: " + e.getMessage(), e);
                            } finally {
                                if (resultSet != null) {
                                    resultSet.close();
                                    resultSet = null;
                                }
                            }
                        }
                        _sql = _sql.replace("#" + paramName + "#", paramValue);
                    }

                    final String sql = _sql;
                    vars.put(C3pConstants.OUTVAR_SQL_STATEMENT, sql);

                    // 将组装成的sql脚本写到本地文件
                    scriptLogger.info("{}.sql\n{}", request.getRequestId(), sql);
                    if (statement.execute(sql)) {
                        final List<C3pScriptParam> c3pScriptParams = ObjectUtils.defaultIfNull(
                                c3pScriptMapper.selectScriptParamByScriptId(results.getParams().get(C3pConstants.PARAM_C3P_COMMAND_ID)),
                                Collections.<C3pScriptParam>emptyList());
                        final List<C3pScriptParam> c3pSqlScriptParams=new ArrayList<C3pScriptParam>(c3pScriptParams.size());
                        final Set<String> keyColumns = new LinkedHashSet<String>();
                        for (C3pScriptParam c3pScriptParam : c3pScriptParams) {
                            if ("Y".equals(c3pScriptParam.getParamType())) {
                                keyColumns.add(c3pScriptParam.getParamName());
                            }
                            if(c3pScriptParam.getParamValue()!=null&&!c3pScriptParam.getParamValue().isEmpty()){
                                c3pSqlScriptParams.add(c3pScriptParam);
                            }
                        }
                        final String KBP_CLASS = StringUtils.defaultString(paramsIn.get("KBP_CLASS"), "DEFAULT");
                        resultSet = statement.getResultSet();
                        StringBuilder stringBuilder = new StringBuilder();
                        StringBuilder stringBuilderUnits = new StringBuilder();
                        ResultSetMetaData metaData = resultSet.getMetaData();
                        int columnCount = metaData.getColumnCount();
                        int rowNo=1;
                        for (int i = 0; i < columnCount; )
                            stringBuilder.append("\t").append(metaData.getColumnLabel(++i));
                        stringBuilder.deleteCharAt(0); // 删除字符串最前面的\t字符
                        while (resultSet.next()) {
                            stringBuilder.append("\n");
                            for (int i = 0; i < columnCount; ) {
                                stringBuilder.append(StringUtils.defaultString(resultSet.getString(++i))).append("\t");
                            }
                            stringBuilder.deleteCharAt(stringBuilder.length() - 1); //删除字符串最后面的\t字符

                            // 输出指标
                            List<String> bzInfo = new ArrayList<String>(keyColumns.size());
                            for (String keyColumn : keyColumns) {
                                bzInfo.add(resultSet.getString(keyColumn));
                            }
                            final String unitId = KBP_CLASS + (bzInfo.isEmpty() ? "" : ":" + StringUtils.join(bzInfo, "_"));
                            for (C3pScriptParam scriptParam : c3pSqlScriptParams) {
                                final HashMap<String, String> kpi = new HashMap<String, String>();
                                kpis.add(kpi);
                                kpi.put("UNIT_ID", unitId);
                                kpi.put("KPI_ID", scriptParam.getParamValue());
                                kpi.put("KPI_VALUE", resultSet.getString(scriptParam.getParamName()));
                                kpi.put("CLL_TIME", format(new Date(), DATE_TIME_FORMAT));
                                kpi.put("EXT_UNIT_ID", extUnitId);
                                kpi.put("POSITION", rowNo+":"+scriptParam.getParamName());
                                kpi.put("COL_NAME", scriptParam.getParamName());
                                kpi.put("DISPLAY_MODE", "2");
                            }
                            stringBuilderUnits.append("\n"+unitId);
                            rowNo++;
                            if (resultSet.getRow() == fetchRows) break; // 到达最大行数,不再继续
                        }
                        vars.put(OUTVAR_SQL_RESULT_CONTENT, stringBuilder.toString());
                        vars.put(OUTVAR_SQL_RESULT_UNIT_ID, stringBuilderUnits.toString());
                        resultSet.last();
                        int row = resultSet.getRow();
                        vars.put(OUTVAR_SQL_RESULT_SIZE, row + (row == statement.getMaxRows() ? "+" : ""));
                    }
                    results.setExecFlag(EXECUTE_SUCCESS);
                    results.setExecResult(vars.get(OUTVAR_SQL_RESULT_CONTENT));
                    // 将sql执行结果写到本地文件
                    scriptOutLogger.info("{}.sql.out\n{}", request.getRequestId(), results.getExecResult());
                } catch (Throwable e) {
                    failed(results, e);
                } finally {
                    vars.put(C3pConstants.SCRIPT_CONTENT, _sql);
                    results.setEndTime(format(new Date(), DATE_TIME_FORMAT));
                    logger.debug("{}: SQL执行完成", request.getRequestId());
                    if (resultSet != null) resultSet.close();
                    statement.close();
                }
                return results;
            }
        };
    }
}
